/**
 * 
 */
package com.aptech.restrant.Bean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.jsp.jstl.sql.Result;

import com.aptech.restrant.Model.*;
import com.aptech.restrant.Bean.*;

/**
 * @author Administrator
 *
 */
public class FoodDataBean {
	
	private ResultSet rs = null;
	private Result result = null;
	
	/**
	 * 返回FoodBean列表
	 * @return
	 */
	public List<FoodBean> getFoods()
	{
		List<FoodBean> foodsList = new ArrayList<FoodBean>();
		String sql = "select * from stuInfo order by stu_ID";
		//获取餐品列表
		try {
			//获取数据库链接，从数据库foodInfo表中得到数据集
			rs = DBConnection.getResultSet(sql);
			
			//读取数据，并保存到foodsList表中
			while (rs.next()) {
				FoodBean food = new FoodBean();
				food.setstu_ID(rs.getInt("stu_ID"));
				food.setstu_name(rs.getString("stu_name"));
				food.setstu_pass(rs.getString("stu_pass"));
				food.setxueyuan(rs.getString("xueyuan"));
				food.setbanji(rs.getString("banji"));
				food.setsoftware(rs.getInt("software"));
				food.setuml(rs.getInt("uml"));
				food.setsnl(rs.getInt("snl"));
				foodsList.add(food);
			}
		} catch (Exception e) {
			System.out.println("获取信息列表时出错");
		}finally{
			//关闭资源
			DBConnection.close_All(rs);
		}
		return foodsList;
	}
	
	/**
	 * 分页返回FoodBean列表
	 * @param pageid 第几页
	 * @param rows 总页数
	 * @return 结果
	 */
	public List<FoodBean> getPageFoodsList(int pageid,int rows)
	{
		int rowBegin=0;//开始行数,表示每页第一条记录在数据库中的行数
		if(pageid>1){
			//按页数取得开始行数,设每页可以显示4条回复
			rowBegin=rows*(pageid-1);
		}
		String sql = "select top "+rows+" * from stuInfo where stu_ID not in(select top "+rowBegin+" stu_ID from stuInfo order by stu_ID) order by stu_ID";
		
		List<FoodBean> foodsList = new ArrayList<FoodBean>();
		//获取餐品列表
		try {
			//获取数据库链接，从数据库foodInfo表中得到数据集
			rs = DBConnection.getResultSet(sql);
			
			//读取数据，并保存到foodsList表中
			while (rs.next()) {
				FoodBean food = new FoodBean();
				food.setstu_ID(rs.getInt("stu_ID"));
				food.setstu_name(rs.getString("stu_name"));
				food.setstu_pass(rs.getString("stu_pass"));
				food.setxueyuan(rs.getString("xueyuan"));
				food.setbanji(rs.getString("banji"));
				food.setsoftware(rs.getInt("software"));
				food.setuml(rs.getInt("uml"));
				food.setsnl(rs.getInt("snl"));
				
				foodsList.add(food);
			}
		} catch (Exception e) {
			System.out.println("获取分页信息列表时出错");
		}finally{
			//关闭资源
			DBConnection.close_All(rs);
		}
		return foodsList;
	}
	/**
	 * 分页返回FoodBean列表
	 * @param pageid 第几页
	 * @param rows 总页数
	 * @return 结果
	 */
	public List<FoodBean> getPageFoods(int pageid,int rows)
	{
		int rowBegin=0;//开始行数,表示每页第一条记录在数据库中的行数
		if(pageid>1){
			//按页数取得开始行数,设每页可以显示4条回复
			rowBegin=rows*(pageid-1);
		}
		String sql = "select top "+rows+" * from stuInfo where stu_ID not in(select top "+rowBegin+" stu_ID from stuInfo order by stu_ID) order by stu_ID";
		List values = new ArrayList();
		values.add(rows);
		values.add(rowBegin);
		
		List<FoodBean> foodsList = null;
		//获取餐品列表
		try {
			//获取数据库链接，从数据库foodInfo表中得到数据集
			result = DBConnection.executeQuery(sql, null);
			
			//读取数据，并保存到foodsList表中
			if(result==null||result.getRowCount()==0){
				return null;
			}else{
				foodsList = new ArrayList<FoodBean>();
				int rowCount = result.getRowCount();
				//读取行数据
				for (int i=0;i<rowCount;i++) {
					Map row = result.getRows()[i];
					
					foodsList.add(setResultFoodBean(row));
				}
			}
			
		} catch (Exception e) {
			System.out.println("Result时获取信息列表时出错");
		}
		return foodsList;
	}
/**
	 * 返回FoodBean实体
	 * @return
	 */
	public FoodBean getFoodsByID(int id)
	{
		FoodBean food = null;
		String sql = "select * from stuInfo where stu_ID = ?";
		//获取餐品列表
		try {
			//获取数据库链接，从数据库foodInfo表中得到数据集
			String [] str = new String[]{""+id};
			rs = DBConnection.getResultSet(sql,str);
			
			if (rs.next()) {
				food = new FoodBean();
				food.setstu_ID(rs.getInt("stu_ID"));
				food.setstu_name(rs.getString("stu_name"));
				food.setstu_pass(rs.getString("stu_pass"));
				food.setxueyuan(rs.getString("xueyuan"));
				food.setbanji(rs.getString("banji"));
				food.setsoftware(rs.getInt("software"));
				food.setuml(rs.getInt("uml"));
				food.setsnl(rs.getInt("snl"));
			}
		} catch (Exception e) {
			System.out.println("获取信息列表时出错");
		}finally{
			//关闭资源
			DBConnection.close_All(rs);
		}
		return food;
	}
	/**
	 * 获取信息的总数目
	 * @return
	 */
	public int getCount(){
		int row = 0;
		String sql = "select count(*) from stuInfo";
		try {
			rs = DBConnection.getResultSet(sql);
			
			//读取数据，并保存到foodsList表中
			if(!rs.next()){
				return row;
			}else{
				row = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("查询信息总数目时错误");
			e.printStackTrace();
		}finally{
			DBConnection.close_All(rs);
		}
		return row;
	}
	
	/**
	 * 设置Food内对象的值
	 * @param row
	 * @return
	 */
	public FoodBean setResultFoodBean(Map row){
		FoodBean food = new FoodBean();
		food.setstu_ID(Integer.parseInt(row.get("stu_ID").toString()));
		food.setstu_name((String)row.get("stu_name"));
		food.setstu_pass((String)row.get("stu_pass"));
		food.setxueyuan((String)row.get("xueyuan"));
		food.setbanji((String)row.get("banji"));
		food.setsoftware(Integer.parseInt(row.get("software").toString()));
		food.setuml(Integer.parseInt(row.get("uml").toString()));
		food.setsnl(Integer.parseInt(row.get("snl").toString()));
		return food;
	}
}
